#include "MonitoringUserSqlite.h"
#define SQNAME "userMs.db"
MonitoringUserSqlite::MonitoringUserSqlite(QObject *parent) : QObject(parent)
{
        db = QSqlDatabase::addDatabase("QSQLITE", "myConnection");
       db.setDatabaseName(SQNAME);
       query = QSqlQuery(db);
}
MonitoringUserSqlite& MonitoringUserSqlite::getInstance() {
    static MonitoringUserSqlite instance;
    return instance;
}

MonitoringUserSqlite::~MonitoringUserSqlite()
{
    if (db.isOpen()) {
           db.close();
       }
}

bool MonitoringUserSqlite::openSqLite(const QString &dbPath)
{
    // 加载 SQLite 数据库驱动
//    db= QSqlDatabase::addDatabase("QSQLITE");
//    // 设置数据库文件名，如果文件不存在会自动创建
//    db.setDatabaseName(dbPath);

    // 打开数据库连接
    if (!db.open()) {
        qDebug() << "无法打开数据库:" << db.lastError().text();
        return false;
    }
    qDebug() << "数据库已成功打开";
    QFileInfo fileInfo(dbPath);
    QString absolutePath = fileInfo.absoluteFilePath();
    qDebug() << "数据库文件绝对路径: " << absolutePath;
    query=QSqlQuery(db);
    return  true;
}

bool MonitoringUserSqlite::createSqLite(const QString &sql)
{

    // 创建一个名为 "users" 的表
    if (!query.exec(sql)) {
        qDebug() << "创建表失败:" << query.lastError().text();
        return false;
    }
    return true;

}

QueryResult MonitoringUserSqlite::insertData(const QString &user, const QString &pass,const QString&email,const QString&role)
{
    if(!optionsSqLite()){
        return QUERY_FAILED;
    }
    if(isUserExists(user)==QUERY_SUCCESS_USER_EXISTS){
            return QUERY_SUCCESS_USER_EXISTS;
    }else {
        const QString insertSql = "INSERT INTO usersmanager (username, password, email, role,created_at,updated_at) VALUES (:username, :password, :email, :role, :created_at ,:updated_at)";
            query.prepare(insertSql);
            query.bindValue(":username", user);
            query.bindValue(":password", pass);
            query.bindValue(":email", email);
            query.bindValue(":role", role);
            query.bindValue(":created_at", MonitoringTool::getNowTimes());
            query.bindValue(":updated_at",  MonitoringTool::getNowTimes());
             db.transaction();
            if (query.exec()) {
                qDebug() << "数据插入成功";
                 db.commit();
               // db.close();
                return QUERY_SUCCESS;
            } else {
                qDebug() << "数据插入失败:" << query.lastError().text();
                db.rollback();
               // db.close();
                return QUERY_FAILED;
            }
    }

}

QueryResult MonitoringUserSqlite::isUserExists(const QString &username)
{
    if(!optionsSqLite()){
        return QUERY_FAILED;
    }
    query.prepare("SELECT * FROM usersmanager WHERE username = :username");
        query.bindValue(":username", username);

        if (query.exec()) {
            if (query.next()) {
                // 查询结果集包含数据，说明用户存在
                qDebug() << "用户存在";
               // db.close();
                return QUERY_SUCCESS_USER_EXISTS;
            } else {
                // 查询结果集为空，说明用户不存在
                qDebug() << "用户不存在";
               // db.close();
                return QUERY_SUCCESS_USER_NOT_EXISTS;
            }
        } else {
            // 查询执行失败
            qDebug() << "查询执行失败:" << query.lastError().text();
           // db.close();
            return QUERY_FAILED;
        }
}

QueryResult MonitoringUserSqlite::changePassword(const QString &username,const QString&password)
{
        if(!optionsSqLite()){
            return QUERY_FAILED;
        }
        query.prepare("UPDATE usersmanager SET password = :newPassword, updated_at = :updatedAt WHERE username = :username");
        query.bindValue(":newPassword", password);
        query.bindValue(":username", username);
        query.bindValue(":updatedAt", MonitoringTool::getNowTimes());
        if (query.exec()) {
            if (query.numRowsAffected() > 0) {
                // 更新成功
               // db.close();
                return QUERY_SUCCESS;
            } else {
                // 未找到匹配的用户
                qDebug() << "未找到匹配的用户，密码更新失败";
               // db.close();
                return QUERY_SUCCESS_USER_NOT_EXISTS;
            }
        } else {
            // 更新执行失败
            qDebug() << "更新执行失败:" << query.lastError().text();
          //  db.close();
            return QUERY_FAILED;
        }

}

QueryResult MonitoringUserSqlite::loginGetData(const QString &username, const QString &password)
{
        if(!optionsSqLite()){
            return QUERY_FAILED;
        }
        query.prepare("SELECT id, username, password FROM usersmanager WHERE username = :username AND password = :password");
        query.bindValue(":username", username);
        query.bindValue(":password", password);

        if (query.exec()) {
            if (query.next()) {
                // 查询结果集包含数据，获取用户信息
//                userInfo.id = query.value(0).toInt();
//                userInfo.username = query.value(1).toString();
//                userInfo.password = query.value(2).toString();
//                qDebug() << "用户信息查询成功:" << userInfo.id << userInfo.username << userInfo.password;
               // db.close();
                return QUERY_SUCCESS;
            } else {
                // 查询结果集为空，说明未找到匹配的用户
                qDebug() << "未找到匹配的用户";
               // db.close();
                return QUERY_SUCCESS_USER_NOT_EXISTS;
            }
        } else {
            // 查询执行失败
            qDebug() << "查询执行失败:" << query.lastError().text();
           // db.close();
            return QUERY_FAILED;
        }
}

QueryResult MonitoringUserSqlite::changeEmail(const QString &username, const QString &email)
{
    if(!optionsSqLite()){
        return QUERY_FAILED;
    }
    query.prepare("UPDATE usersmanager SET email = :newemail, updated_at = :updatedAt WHERE username = :username");
    query.bindValue(":newemail", email);
    query.bindValue(":username", username);
    query.bindValue(":updatedAt", MonitoringTool::getNowTimes());
    if (query.exec()) {
        if (query.numRowsAffected() > 0) {
            // 更新成功
           // db.close();
            return QUERY_SUCCESS;
        } else {
            // 未找到匹配的用户
            qDebug() << "未找到匹配的用户，密码更新失败";
           // db.close();
            return QUERY_SUCCESS_USER_NOT_EXISTS;
        }
    } else {
        // 更新执行失败
        qDebug() << "更新执行失败:" << query.lastError().text();
      //  db.close();
        return QUERY_FAILED;
    }
}


bool MonitoringUserSqlite::optionsSqLite()
{

    if(!openSqLite(SQNAME)){
       // db.close();
        return false;
    }
    if(!createSqLite("CREATE TABLE IF NOT EXISTS usersmanager ("
                     "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                     "username TEXT NOT NULL, "
                     "password TEXT NOT NULL, "
                     "email TEXT, "
                     "role TEXT NOT NULL,"
                     "created_at TEXT, "
                     "updated_at TEXT"
                     ")")){
       // db.close();
        return false;
    }
    return true;
}

userModel* MonitoringUserSqlite::getSqLite(const QString &username)
{
    if(!optionsSqLite()){
        return nullptr;
    }

     query.prepare("SELECT id, username, password, email, role,created_at ,updated_at  FROM usersmanager WHERE username = :username");
      query.bindValue(":username", username);
      qDebug()<<"ffff"<<username;
    if (query.exec()) {
        while (query.next()) {
            int id = query.value(0).toInt();
            QString name = query.value(1).toString();
            QString password = query.value(2).toString();
            QString email = query.value(3).toString();
            QString role = query.value(4).toString();
             QString created_at = query.value(5).toString();
              QString updated_at = query.value(6).toString();
            qDebug() << "ID:" << id << "Name:" << name << "Age:" << password<< "email:" << email<< "role:" << role;
            userModel*model=new userModel(id,name,email,role,created_at,updated_at,password);
            return model;
        }
       // db.close();
        qDebug()<<"==="<<query.next();
        return nullptr;
    } else {
        qDebug() << "查询数据失败:" << query.lastError().text();
        return nullptr;
    }

}
